package com.topboya.officeuser.controller;

import com.topboya.officeuser.entity.BoyaSalesInfo;
import com.topboya.officeuser.service.BoyaSalesInfoService;
import com.topboya.officeuser.service.serviceImpl.BoyaSalesInfoServiceImpl;
import com.topboya.officeuser.utils.Result;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @className BoyaSalesInfoController
 * @Author xiaxin
 * @Date 2020/12/8 15:14
 * @Version 1.0
 **/
@RestController
public class BoyaSalesInfoController {
    @Autowired
    BoyaSalesInfoService boyaSalesInfoService;

    @ApiOperation(value = "根据Id查询公司或部门")
    @GetMapping("geteeee")
    public Result getOfficeById() throws IOException, OpenXML4JException, SAXException {
        List<BoyaSalesInfo> boyaSalesInfoList = new ArrayList<>();

        BoyaSalesInfoService boyaSalesInfoService = new BoyaSalesInfoServiceImpl();


        int totalRow = 0;
        //File test = new File(".");
        //String file = test.getAbsolutePath() + "/src/main/resources/test.xlsx";
        String file = "C:\\Users\\boya\\Desktop\\公司项目\\excel\\未结费用明细 - 副本.xlsx";
        OPCPackage pkg = OPCPackage.open(file);
        XSSFReader r = new XSSFReader(pkg);
        InputStream is = r.getSheet("rId1");
        //debug 查看转换的xml原始文件，方便理解后面解析时的处理,
        byte[] isBytes = IOUtils.toByteArray(is);
//        Excel.streamOut(new ByteArrayInputStream(isBytes));

        //下面是SST 的索引会用到的
        SharedStringsTable sst = r.getSharedStringsTable();
        System.out.println("excel的共享字符表sst------------------start");
//        sst.writeTo(System.out);
        System.out.println();
        System.out.println("excel的共享字符表sst------------------end");

        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
//        XMLReader parser2 = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        List<List<String>> container = new ArrayList<>();
        parser.setContentHandler(new Myhandler(sst, container));
//        ExcelXlsxReaderWithDefaultHandler a = new ExcelXlsxReaderWithDefaultHandler(totalRow);
//        parser2.setContentHandler(a);
//
//        InputSource inputSource = new InputSource(new ByteArrayInputStream(isBytes));
//        parser2.parse(inputSource);
//        System.out.println(a.getTotalRow());
        InputSource inputSource2 = new InputSource(new ByteArrayInputStream(isBytes));
        parser.parse(inputSource2);
//        container.forEach(System.out::println);
        System.out.println(container.size());
        for (int i = 0; i < container.size(); i++) {
            List<String> strings = container.get(i);
            if(i==0){
                continue;
            }
            if(StringUtils.isBlank(strings.get(0).trim())){
                strings.add("id为必填项");
            } else{
                String id  =              strings.get(0).trim();
                String remarks =          strings.get(1).trim();
                String uploadYear =       strings.get(2).trim();
                String uploadMonth =      strings.get(3).trim();
                String productName =      strings.get(4).trim();
                String productUnit =        strings.get(5).trim();
                String productFactory =   strings.get(6).trim();
                String supplyPrice =      strings.get(7).trim();
                String salesAmount =       strings.get(8).trim();

                String inComeDate =        strings.get(9).trim();
                String salesCount =        strings.get(10).trim();
                String business =          strings.get(11).trim();
                String orgCode =           strings.get(12).trim();
                String orgName =           strings.get(13).trim();
                String orgLevel =          strings.get(14).trim();
                String orgCategory =        strings.get(15).trim();
                String extraOrgName =       strings.get(16).trim();
                String orgArea =            strings.get(17).trim();
                String userName =           strings.get(18).trim();
                String developer =          strings.get(19).trim();
                String userBelong =         strings.get(20).trim();
                String superiorName =       strings.get(21).trim();
                String distributionGroup =  strings.get(22).trim();
                String distributionLeader =  strings.get(23).trim();
                String place =                strings.get(24).trim();
                String lotNumber =             strings.get(25).trim();
                BoyaSalesInfo boyaSalesInfo = new BoyaSalesInfo();
                boyaSalesInfo.setId(id);
                boyaSalesInfo.setRemarks(remarks);
                boyaSalesInfo.setUploadYear(Integer.parseInt(uploadYear));
                boyaSalesInfo.setUploadMonth(Integer.parseInt(uploadMonth));
                boyaSalesInfo.setProductName(productName);
                boyaSalesInfo.setProductUnit(productUnit);
                boyaSalesInfo.setProductFactory(productFactory);
                boyaSalesInfo.setSupplyPrice(supplyPrice);
//                boyaSalesInfo.setInComeDate(DateUtils.parseDate(inComeDate,"yyyy/MM/dd"));
                boyaSalesInfo.setSalesAmount(Float.parseFloat(salesAmount));
                boyaSalesInfo.setSalesCount(Double.parseDouble(salesCount));
                boyaSalesInfo.setBusiness(business);
                boyaSalesInfo.setOrgCode(orgCode);
                boyaSalesInfo.setOrgName(orgName);
                boyaSalesInfo.setOrgLevel(orgLevel);
                boyaSalesInfo.setOrgCategory(orgCategory);
                boyaSalesInfo.setExtraOrgName(extraOrgName);
                boyaSalesInfo.setOrgArea(orgArea);
                boyaSalesInfo.setDeveloper(developer);
                boyaSalesInfo.setUserName(userName);
                boyaSalesInfo.setUserBelong(userBelong);
                boyaSalesInfo.setSuperiorName(superiorName);
                boyaSalesInfo.setDistributionGroup(distributionGroup);
                boyaSalesInfo.setDistributionLeader(distributionLeader);
                boyaSalesInfo.setPlace(place);
                boyaSalesInfo.setLotNumber(lotNumber);
                boyaSalesInfoList.add(boyaSalesInfo);
                if(boyaSalesInfoList.size()>1){

                    boyaSalesInfoService.insertBoyaSalesInfoBatch(boyaSalesInfoList);
                    boyaSalesInfoList.clear();
                }
                //boyaSalesInfo.setShipDate();
                //boyaSalesInfo.setStartTime();
                //boyaSalesInfo.setEndTime();
                //boyaSalesInfo.setSubmitFlag();
            }
        }
        is.close();


        return null;
    }

}
